﻿namespace AnyOffice.DAL
{
    using AnyOffice.Components;
    using AnyOffice.Model;
    using log4net;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection;
    using System.Web;

    public class UsersDao
    {
        private static ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public bool ChangePassword(SqlConnection conn, string userId, string oldPassword, string newPassword)
        {
            string commandText = "DECLARE @Ret bit IF EXISTS(SELECT [UserId] FROM [dbo].[Users] WHERE [UserId]= @UserId AND [Password] = @OldPassword) BEGIN UPDATE [dbo].[Users] SET [Password] = @NewPassword WHERE [UserId] = @UserId SET @Ret =1 END ELSE SET @Ret =0 SELECT @Ret";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@OldPassword", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = oldPassword;
            commandParameters[2] = new SqlParameter("@NewPassword", SqlDbType.NVarChar, 50);
            commandParameters[2].Value = newPassword;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public bool ChangeState(SqlConnection conn, string userId, int state)
        {
            string commandText = "UPDATE [dbo].[Users] SET [State] = @State, [LastPassTime] = GETDATE() WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@State", SqlDbType.Int);
            commandParameters[1].Value = state;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public bool Delete(SqlConnection conn, string userId)
        {
            string commandText = "SET NOCOUNT ON UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Email] WHERE [FromId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Email] WHERE [FromId] = @UserId UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Email] WHERE [ToId] = @UserId AND ISNULL([AttachmentId],'') <> '' AND [AttachmentId] NOT IN ( SELECT [AttachmentId] FROM [dbo].[Email] WHERE [ToId] <> @UserId AND ISNULL([AttachmentId],'') <> '' ) ) DELETE FROM [dbo].[Email] WHERE [ToId] = @UserId DELETE FROM [dbo].[Email_Box] WHERE [UserId] = @UserId DELETE FROM [dbo].[Sms] WHERE [FromId] = @UserId OR [ToId] = @UserId DELETE FROM [dbo].[Mobile] WHERE [FromId] = @UserId OR [ToId] = @UserId DELETE FROM [dbo].[Mobile_Receive] WHERE [FromId] = @UserId OR [ToId] = @UserId DELETE FROM [dbo].[Calendar] WHERE [UserId] = @UserId OR [ManagerId] = @UserId DELETE FROM [dbo].[Diary_Comment] WHERE [UserId] = @UserId OR [DiaryId] IN ( SELECT [DiaryId] FROM [dbo].[Diary] WHERE [UserId] = @UserId ) UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Diary] WHERE [UserId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Diary] WHERE [UserId] = @UserId UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Report] WHERE [UserId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Report] WHERE [UserId] = @UserId UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Event] WHERE [UserId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Event] WHERE [UserId] = @UserId DELETE FROM [dbo].[Address] WHERE [UserId] = @UserId DELETE FROM [dbo].[Address_Group] WHERE [UserId] = @UserId DELETE FROM [dbo].[News_Comment] WHERE [UserId] = @UserId OR [NewsId] IN ( SELECT [NewsId] FROM [dbo].[News] WHERE [FromId] = @UserId ) UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[News] WHERE [FromId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[News] WHERE [FromId] = @UserId UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Notify] WHERE [FromId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Notify] WHERE [FromId] = @UserId DELETE FROM [dbo].[Vote_Item] WHERE [VoteId] IN ( SELECT [VoteId] FROM [dbo].[Vote] WHERE [UserId] = @UserId ) DELETE FROM [dbo].[Vote] WHERE [UserId] = @UserId UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Plan_Work] WHERE [Creator] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Plan_Work] WHERE [Creator] = @UserId DELETE FROM [dbo].[Hrms] WHERE [UserId] = @UserId DELETE FROM [dbo].[Office_Transhistory] WHERE [Borrower] = @UserId OR [Operator] = @UserId DELETE FROM [dbo].[Meeting] WHERE [Proposer] = @UserId DELETE FROM [dbo].[Vehicle_Usage] WHERE [Proposer] = @UserId UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Bbs_Comment] WHERE [ParentId] IN ( SELECT [CommentId] FROM [dbo].[Bbs_Comment] WHERE [ParentId] = 0 AND [UserId] = @UserId ) AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Bbs_Comment] WHERE [ParentId] IN ( SELECT [CommentId] FROM [dbo].[Bbs_Comment] WHERE [ParentId] = 0 AND [UserId] = @UserId ) UPDATE [dbo].[Attachment] SET [DeleteFlag] = 1 WHERE [AttachmentId] IN ( SELECT [AttachmentId] FROM [dbo].[Bbs_Comment] WHERE [ParentId] = 0 AND [UserId] = @UserId AND ISNULL([AttachmentId],'') <> '' ) DELETE FROM [dbo].[Bbs_Comment] WHERE [ParentId] = 0 AND [UserId] = @UserId DELETE FROM [dbo].[NetMeeting_Messages] WHERE [UserId] = @UserId OR [SessionId] IN ( SELECT [SessionId] FROM [dbo].[NetMeeting_Sessions] WHERE [FromId] = @UserId ) DELETE FROM [dbo].[NetMeeting_User] WHERE [UserId] = @UserId OR [SessionId] IN ( SELECT [SessionId] FROM [dbo].[NetMeeting_Sessions] WHERE [FromId] = @UserId ) DELETE FROM [dbo].[NetMeeting_Sessions] WHERE [FromId] = @UserId DELETE FROM [dbo].[Chat_Messages] WHERE [UserId] = @UserId DELETE FROM [dbo].[Chat_User] WHERE [UserId] = @UserId DELETE FROM [dbo].[Users] WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            ConfigManager manager = new ConfigManager();
            if (manager.RtxEnabled)
            {
                try
                {
                    Type type = Assembly.LoadFrom(HttpContext.Current.Server.MapPath("/bin/RTX.dll")).GetType("RTX.RtxSdk", true);
                    object obj2 = Activator.CreateInstance(type);
                    MethodInfo method = type.GetMethod("DelUser");
                    object[] parameters = new object[] { userId };
                    method.Invoke(obj2, parameters);
                }
                catch (Exception exception)
                {
                    if (log.IsErrorEnabled)
                    {
                        log.Error("RTX", exception);
                    }
                }
            }
            return true;
        }

        public void Exit(SqlConnection conn, string userId)
        {
            string commandText = "UPDATE [dbo].[Users] SET [State] = 0 WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
        }

        public bool Insert(SqlConnection conn, UsersData data)
        {
            string commandText = "INSERT [dbo].[Users] ( [UserId], [UserName], [DeptId], [UserNo], [PrivId], [PostPriv], [PostDept], [DutyType], [AttendCard], [EmailCapacity], [FolderCapacity], [Password], [Sex], [Birthday], [IsHideBirthday], [Theme], [Mobile], [IsHideMobile], [MytableLeft], [MytableRight], [BindIp], [UsingKey] ) VALUES ( @UserId, @UserName, @DeptId, @UserNo, @PrivId, @PostPriv, @PostDept, @DutyType, @AttendCard, @EmailCapacity, @FolderCapacity, @Password, @Sex, @Birthday, @IsHideBirthday, @Theme, @Mobile, @IsHideMobile, @MytableLeft, @MytableRight, @BindIp, @UsingKey )";
            SqlParameter[] commandParameters = new SqlParameter[0x16];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.UserName;
            commandParameters[2] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[2].Value = data.DeptId;
            commandParameters[3] = new SqlParameter("@UserNo", SqlDbType.Int);
            commandParameters[3].Value = data.UserNo;
            commandParameters[4] = new SqlParameter("@PrivId", SqlDbType.Int);
            commandParameters[4].Value = data.PrivId;
            commandParameters[5] = new SqlParameter("@PostPriv", SqlDbType.NVarChar, 50);
            commandParameters[5].Value = data.PostPriv;
            commandParameters[6] = new SqlParameter("@PostDept", SqlDbType.NText);
            if (data.PostDeptIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.PostDept;
            }
            commandParameters[7] = new SqlParameter("@DutyType", SqlDbType.Int);
            commandParameters[7].Value = data.DutyType;
            commandParameters[8] = new SqlParameter("@AttendCard", SqlDbType.NVarChar, 50);
            if (data.AttendCardIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.AttendCard;
            }
            commandParameters[9] = new SqlParameter("@EmailCapacity", SqlDbType.Int);
            commandParameters[9].Value = data.EmailCapacity;
            commandParameters[10] = new SqlParameter("@FolderCapacity", SqlDbType.Int);
            commandParameters[10].Value = data.FolderCapacity;
            commandParameters[11] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
            if (data.PasswordIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.Password;
            }
            commandParameters[12] = new SqlParameter("@Sex", SqlDbType.Bit);
            commandParameters[12].Value = data.Sex;
            commandParameters[13] = new SqlParameter("@Birthday", SqlDbType.DateTime);
            commandParameters[13].Value = data.Birthday;
            commandParameters[14] = new SqlParameter("@IsHideBirthday", SqlDbType.Bit);
            commandParameters[14].Value = data.IsHideBirthday;
            commandParameters[15] = new SqlParameter("@Theme", SqlDbType.Int);
            commandParameters[15].Value = data.Theme;
            commandParameters[0x10] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            if (data.MobileIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.Mobile;
            }
            commandParameters[0x11] = new SqlParameter("@IsHideMobile", SqlDbType.Bit);
            commandParameters[0x11].Value = data.IsHideMobile;
            commandParameters[0x12] = new SqlParameter("@MytableLeft", SqlDbType.NVarChar, 200);
            if (data.MytableLeftIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.MytableLeft;
            }
            commandParameters[0x13] = new SqlParameter("@MytableRight", SqlDbType.NVarChar, 200);
            if (data.MytableRightIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.MytableRight;
            }
            commandParameters[20] = new SqlParameter("@BindIp", SqlDbType.NVarChar, 100);
            if (data.BindIpIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.BindIp;
            }
            commandParameters[0x15] = new SqlParameter("@UsingKey", SqlDbType.Bit);
            commandParameters[0x15].Value = data.UsingKey;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            ConfigManager manager = new ConfigManager();
            if (manager.RtxEnabled)
            {
                try
                {
                    Type type = Assembly.LoadFrom(HttpContext.Current.Server.MapPath("/bin/RTX.dll")).GetType("RTX.RtxSdk", true);
                    object obj2 = Activator.CreateInstance(type);
                    MethodInfo method = type.GetMethod("AddUser");
                    object[] parameters = new object[] { data.UserId, data.DeptId, data.UserName, data.PasswordIsNull ? "" : data.Password };
                    method.Invoke(obj2, parameters);
                }
                catch (Exception exception)
                {
                    if (log.IsErrorEnabled)
                    {
                        log.Error("RTX", exception);
                    }
                }
            }
            return true;
        }

        public bool IsExists(SqlConnection conn, string userId)
        {
            string commandText = "DECLARE @Ret bit IF EXISTS( SELECT [UserId] FROM [dbo].[Users] WHERE [UserId] = @UserId OR [UserId2] = @UserId) SET @Ret = 1 ELSE SET @Ret = 0 SELECT @Ret";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public DataTable Login(SqlConnection conn, string O0Ol1O, string password)
        {
            string commandText = "IF EXISTS(SELECT [UserId] FROM [dbo].[Users] WHERE ([UserId]= @UserId OR [UserId2]= @UserId) AND [Password] = @Password AND [Disabled] = 0) BEGIN UPDATE [dbo].[Users] SET [LastVisitTime] = getdate(), [LastPassTime] = getdate(), [State] = 1 WHERE [UserId]= @UserId OR [UserId2]= @UserId UPDATE [dbo].[Users] SET [State] = 0 WHERE DATEDIFF(s,[LastPassTime],getdate())>120 END SELECT [UserId], [Disabled], [MenuType], [BindIp], [UsingKey] FROM [dbo].[Users] WHERE ([UserId]= @UserId OR [UserId2]= @UserId) AND [Password] = @Password";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = O0Ol1O;
            commandParameters[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = password;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public bool ResetPassword(SqlConnection conn, string userId, string password)
        {
            string commandText = "UPDATE [dbo].[Users] SET [Password] = @Password WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = password;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public DataTable SelectAll(SqlConnection conn)
        {
            string commandText = "SELECT [Users].[UserId], [Users].[UserName] FROM [dbo].[Users], [dbo].[Department] WHERE [Users].[DeptId] = [Department].[DeptId] ORDER BY [Department].[DeptNo] ASC, [Users].[UserNo] ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectBaseAll(SqlConnection conn)
        {
            string commandText = "SELECT [Users].[UserId], [Users].[UserName], [Department].[ParentDept], [Department].[DeptName] FROM [dbo].[Users], [dbo].[Department] WHERE [Users].[DeptId] = [Department].[DeptId] AND [Users].[DeptId] <> -1 ORDER BY [Department].[DeptNo] ASC, [Users].[UserNo] ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectBaseByDeptId(SqlConnection conn, int DeptId)
        {
            string commandText = "SELECT [UserId], [UserName] FROM [dbo].[Users] WHERE [DeptId] = @DeptId ORDER BY [UserNo] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DeptId", SqlDbType.Int) };
            commandParameters[0].Value = DeptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectBaseByPrivId(SqlConnection conn, int privId)
        {
            string commandText = "SELECT [Users].[UserId], [Users].[UserName] FROM [dbo].[Users], [dbo].[Department] WHERE [Users].[PrivId] = @PrivId AND [Users].[DeptId] = [Department].[DeptId] AND [Users].[DeptId] <> -1 ORDER BY [Department].[DeptNo] ASC, [Users].[UserNo] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@PrivId", SqlDbType.Int) };
            commandParameters[0].Value = privId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectByDeptId(SqlConnection conn, int deptId)
        {
            string commandText = "SELECT [Users].[UserId], [Users].[UserId2], [Users].[UserNo], [Users].[UserName], [Users].[Password], [Users].[PrivId], [Users].[PostPriv], [Users].[PostDept], [Users].[DeptId], [Users].[CanBroadcast], [Users].[Sex], [Users].[Birthday], [Users].[IsHideBirthday], [Users].[TelNoDept], [Users].[FaxNoDept], [Users].[AddressHome], [Users].[ZipHome], [Users].[TelHome], [Users].[Mobile], [Users].[IsHideMobile], [Users].[Bp], [Users].[Email], [Users].[Oicq], [Users].[Icq], [Users].[Msn], [Users].[Skype], [Users].[NickName], [Users].[Avatar], [Users].[MenuType], [Users].[MenuHide], [Users].[SmsOn], [Users].[CallSound], [Users].[Theme], [Users].[EmailCapacity], [Users].[FolderCapacity], [Users].[OnlineTime], [Users].[DutyType], [Users].[AttendCard], [Users].[State], [Users].[LastPassTime], [Users].[LastVisitTime], [Users].[LastIp], [Users].[Disabled], [Users].[BindIp], [Users].[UsingKey], [Users].[UGuid], [Users].[UKey], [Users].[UExt], [User_Priv].[PrivName], [Attend_Config].[DutyName] FROM [dbo].[Users], [dbo].[User_Priv], [dbo].[Attend_Config] WHERE [Users].[DeptId] = @DeptId AND [Users].[PrivId] = [User_Priv].[PrivId] AND [Users].[DutyType] = [Attend_Config].[DutyTypeId] ORDER BY [Users].[UserNo] ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@DeptId", SqlDbType.Int) };
            commandParameters[0].Value = deptId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectByUserId(SqlConnection conn, string userId)
        {
            string commandText = "SELECT [Users].[UserId], [Users].[UserId2], [Users].[UserNo], [Users].[UserName], [Users].[Password], [Users].[PrivId], [Users].[PostPriv], [Users].[PostDept], [Users].[DeptId], [Users].[CanBroadcast], [Users].[Sex], [Users].[Birthday], [Users].[IsHideBirthday], [Users].[TelNoDept], [Users].[FaxNoDept], [Users].[AddressHome], [Users].[ZipHome], [Users].[TelHome], [Users].[Mobile], [Users].[IsHideMobile], [Users].[Bp], [Users].[Email], [Users].[Oicq], [Users].[Icq], [Users].[Msn], [Users].[Skype], SUBSTRING([Users].[Photo],1,10) AS Photo, [Users].[NickName], [Users].[Avatar], [Users].[MenuType], [Users].[MenuHide], [Users].[SmsOn], [Users].[CallSound], [Users].[Theme], [Users].[EmailCapacity], [Users].[FolderCapacity], [Users].[OnlineTime], [Users].[DutyType], [Users].[AttendCard], [Users].[State], [Users].[LastPassTime], [Users].[LastVisitTime], [Users].[LastIp], [Users].[Disabled], [Users].[BindIp], [Users].[UsingKey], [Users].[UGuid], [Users].[UKey], [Users].[UExt], dbo.GetDeptNames(CAST([Users].[PostDept] AS nvarchar(2000))) AS PostDeptNames, [Department].[DeptName], [User_Priv].[PrivName], [Attend_Config].[DutyName], [User_Priv].[FuncIdStr] FROM [dbo].[Users], [dbo].[Department], [dbo].[User_Priv], [dbo].[Attend_Config] WHERE [Users].[UserId] = @UserId AND [Users].[DeptId] = [Department].[DeptId] AND [Users].[PrivId] = [User_Priv].[PrivId] AND [Users].[DutyType] = [Attend_Config].[DutyTypeId]";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectDimission(SqlConnection conn)
        {
            string commandText = "SELECT [Users].[UserId], [Users].[UserName], [User_Priv].[PrivName] FROM [dbo].[Users], [dbo].[User_Priv] WHERE [Users].[DeptId] = -1 AND [Users].[PrivId] = [User_Priv].[PrivId] ORDER BY [UserNo] ASC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectOnline(SqlConnection conn, string userId)
        {
            string commandText = "UPDATE [dbo].[Users] SET [LastPassTime] = GETDATE() WHERE [UserId] = @UserId UPDATE [dbo].[Users] SET [State] = 0 WHERE DATEDIFF(s,[LastPassTime],getdate())>120 DECLARE @Online TABLE( UserId nvarchar(20), UserNo int, UserName nvarchar(20), State int, LastIp nvarchar(20), DeptId int, ParentDept int, DeptName nvarchar(50), DeptNo int ) INSERT INTO @Online SELECT [Users].[UserId], [Users].[UserNo], [Users].[UserName], [Users].[State], [Users].[LastIp], [Department].[DeptId], [Department].[ParentDept], [Department].[DeptName], [Department].[DeptNo] FROM [dbo].[Users], [dbo].[Department] WHERE [Users].[DeptId] = [Department].[DeptId] AND [Users].[DeptId] <> -1 AND DATEDIFF(s,[Users].[LastPassTime],GETDATE()) <=120 AND [Users].[State] > 0 SELECT a.*,b.DeptNo AS ParentNo FROM @Online AS a LEFT OUTER JOIN [dbo].[Department] AS b ON a.ParentDept = b.DeptId ORDER BY ParentNo ASC, a.DeptNo ASC, a.DeptId ASC, a.UserNo ASC, a.UserId ASC";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectOnlineTimeTop(SqlConnection conn)
        {
            string commandText = "SELECT TOP 20 [Users].[UserId], [Users].[UserName], [Users].[OnlineTime], [Department].[DeptName] FROM [dbo].[Users], [dbo].[Department] WHERE [Users].[DeptId] = [Department].[DeptId] AND [Users].[DeptId] <> -1 AND [OnlineTime] > 0 ORDER BY [Users].[OnlineTime] DESC";
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public bool SetUserId2(SqlConnection conn, string userId, string userId2)
        {
            string commandText = "IF NOT EXISTS( SELECT [UserId] FROM [dbo].[Users] WHERE [UserId] <> @UserId AND ( [UserId] = @UserId2 OR [UserId2] = @UserId2 ) ) OR @UserId2 IS NULL BEGIN UPDATE [dbo].[Users] SET [UserId2] = @UserId2 WHERE [UserId] = @UserId SELECT 1 END ELSE SELECT 0";
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@UserId2", SqlDbType.NVarChar, 20);
            if (userId2 == string.Empty)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = userId2;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            return ((obj2 != null) && Convert.ToBoolean(obj2));
        }

        public bool Update(SqlConnection conn, UsersData data)
        {
            string commandText = "UPDATE [dbo].[Users] SET [UserId] = @UserId, [UserName] = @UserName, [DeptId] = @DeptId, [UserNo] = @UserNo, [PrivId] = @PrivId, [PostPriv] = @PostPriv, [PostDept] = @PostDept, [DutyType] = @DutyType, [AttendCard] = @AttendCard, [EmailCapacity] = @EmailCapacity, [FolderCapacity] = @FolderCapacity, [Disabled] = @Disabled, [Sex] = @Sex, [Birthday] = @Birthday, [IsHideBirthday] = @IsHideBirthday, [Theme] = @Theme, [Mobile] = @Mobile, [IsHideMobile] = @IsHideMobile, [BindIp] = @BindIp, [UsingKey] = @UsingKey WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[20];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.UserName;
            commandParameters[2] = new SqlParameter("@DeptId", SqlDbType.Int);
            commandParameters[2].Value = data.DeptId;
            commandParameters[3] = new SqlParameter("@UserNo", SqlDbType.Int);
            commandParameters[3].Value = data.UserNo;
            commandParameters[4] = new SqlParameter("@PrivId", SqlDbType.Int);
            commandParameters[4].Value = data.PrivId;
            commandParameters[5] = new SqlParameter("@PostPriv", SqlDbType.NVarChar, 50);
            commandParameters[5].Value = data.PostPriv;
            commandParameters[6] = new SqlParameter("@PostDept", SqlDbType.NText);
            if (data.PostDeptIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.PostDept;
            }
            commandParameters[7] = new SqlParameter("@DutyType", SqlDbType.Int);
            commandParameters[7].Value = data.DutyType;
            commandParameters[8] = new SqlParameter("@AttendCard", SqlDbType.NVarChar, 50);
            if (data.AttendCardIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.AttendCard;
            }
            commandParameters[9] = new SqlParameter("@EmailCapacity", SqlDbType.Int);
            commandParameters[9].Value = data.EmailCapacity;
            commandParameters[10] = new SqlParameter("@FolderCapacity", SqlDbType.Int);
            commandParameters[10].Value = data.FolderCapacity;
            commandParameters[11] = new SqlParameter("@Disabled", SqlDbType.Bit);
            commandParameters[11].Value = data.Disabled;
            commandParameters[12] = new SqlParameter("@Sex", SqlDbType.Bit);
            commandParameters[12].Value = data.Sex;
            commandParameters[13] = new SqlParameter("@Birthday", SqlDbType.DateTime);
            commandParameters[13].Value = data.Birthday;
            commandParameters[14] = new SqlParameter("@IsHideBirthday", SqlDbType.Bit);
            commandParameters[14].Value = data.IsHideBirthday;
            commandParameters[15] = new SqlParameter("@Theme", SqlDbType.Int);
            commandParameters[15].Value = data.Theme;
            commandParameters[0x10] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            if (data.MobileIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.Mobile;
            }
            commandParameters[0x11] = new SqlParameter("@IsHideMobile", SqlDbType.Bit);
            commandParameters[0x11].Value = data.IsHideMobile;
            commandParameters[0x12] = new SqlParameter("@BindIp", SqlDbType.NVarChar, 100);
            if (data.BindIpIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.BindIp;
            }
            commandParameters[0x13] = new SqlParameter("@UsingKey", SqlDbType.Bit);
            commandParameters[0x13].Value = data.UsingKey;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            ConfigManager manager = new ConfigManager();
            if (manager.RtxEnabled)
            {
                try
                {
                    Type type = Assembly.LoadFrom(HttpContext.Current.Server.MapPath("/bin/RTX.dll")).GetType("RTX.RtxSdk", true);
                    object obj2 = Activator.CreateInstance(type);
                    MethodInfo method = type.GetMethod("SetUser");
                    object[] parameters = new object[] { data.UserId, data.DeptId, data.UserName };
                    method.Invoke(obj2, parameters);
                }
                catch (Exception exception)
                {
                    if (log.IsErrorEnabled)
                    {
                        log.Error("RTX", exception);
                    }
                }
            }
            return true;
        }

        public bool UpdatePersonal(SqlConnection conn, UsersData data)
        {
            string commandText = "UPDATE [dbo].[Users] SET [Sex] = @Sex, [Birthday] = @Birthday, [IsHideBirthday] = @IsHideBirthday, [TelNoDept] = @TelNoDept, [FaxNoDept] = @FaxNoDept, [AddressHome] = @AddressHome, [ZipHome] = @ZipHome, [TelHome] = @TelHome, [Mobile] = @Mobile, [IsHideMobile] = @IsHideMobile, [Bp] = @Bp, [Email] = @Email, [Oicq] = @Oicq, [Icq] = @Icq, [Msn] = @Msn, [Skype] = @Skype WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[0x11];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@Sex", SqlDbType.Bit);
            commandParameters[1].Value = data.Sex;
            commandParameters[2] = new SqlParameter("@Birthday", SqlDbType.DateTime);
            commandParameters[2].Value = data.Birthday;
            commandParameters[3] = new SqlParameter("@TelNoDept", SqlDbType.NVarChar, 50);
            if (data.TelNoDeptIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.TelNoDept;
            }
            commandParameters[4] = new SqlParameter("@FaxNoDept", SqlDbType.NVarChar, 50);
            if (data.FaxNoDeptIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.FaxNoDept;
            }
            commandParameters[5] = new SqlParameter("@AddressHome", SqlDbType.NVarChar, 100);
            if (data.AddressHomeIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.AddressHome;
            }
            commandParameters[6] = new SqlParameter("@ZipHome", SqlDbType.NVarChar, 50);
            if (data.ZipHomeIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.ZipHome;
            }
            commandParameters[7] = new SqlParameter("@TelHome", SqlDbType.NVarChar, 50);
            if (data.TelHomeIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.TelHome;
            }
            commandParameters[8] = new SqlParameter("@Mobile", SqlDbType.NVarChar, 50);
            if (data.MobileIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Mobile;
            }
            commandParameters[9] = new SqlParameter("@IsHideMobile", SqlDbType.Bit);
            commandParameters[9].Value = data.IsHideMobile;
            commandParameters[10] = new SqlParameter("@Bp", SqlDbType.NVarChar, 50);
            if (data.BpIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.Bp;
            }
            commandParameters[11] = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
            if (data.EmailIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.Email;
            }
            commandParameters[12] = new SqlParameter("@Oicq", SqlDbType.NVarChar, 50);
            if (data.OicqIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.Oicq;
            }
            commandParameters[13] = new SqlParameter("@Icq", SqlDbType.NVarChar, 50);
            if (data.IcqIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.Icq;
            }
            commandParameters[14] = new SqlParameter("@Msn", SqlDbType.NVarChar, 50);
            if (data.MsnIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.Msn;
            }
            commandParameters[15] = new SqlParameter("@IsHideBirthday", SqlDbType.Bit);
            commandParameters[15].Value = data.IsHideBirthday;
            commandParameters[0x10] = new SqlParameter("@Skype", SqlDbType.NVarChar, 50);
            if (data.SkypeIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.Skype;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            ConfigManager manager = new ConfigManager();
            if (manager.RtxEnabled)
            {
                try
                {
                    Type type = Assembly.LoadFrom(HttpContext.Current.Server.MapPath("/bin/RTX.dll")).GetType("RTX.RtxSdk", true);
                    object obj2 = Activator.CreateInstance(type);
                    MethodInfo method = type.GetMethod("SetUserInfo");
                    object[] parameters = new object[] { data.UserId, data.EmailIsNull ? "" : data.Email, data.MobileIsNull ? "" : data.Mobile };
                    method.Invoke(obj2, parameters);
                }
                catch (Exception exception)
                {
                    if (log.IsErrorEnabled)
                    {
                        log.Error("RTX", exception);
                    }
                }
            }
            return true;
        }

        public bool UpdatePersonality(SqlConnection conn, UsersData data)
        {
            string commandText = "UPDATE [dbo].[Users] SET [NickName] = @NickName, [Avatar] = @Avatar, [Theme] = @Theme, [MenuType] = @MenuType, [MenuHide] = @MenuHide, [SmsOn] = @SmsOn, [CallSound] = @CallSound WHERE [UserId] = @UserId";
            SqlParameter[] commandParameters = new SqlParameter[8];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@NickName", SqlDbType.NVarChar, 50);
            if (data.NickNameIsNull)
            {
                commandParameters[1].Value = DBNull.Value;
            }
            else
            {
                commandParameters[1].Value = data.NickName;
            }
            commandParameters[2] = new SqlParameter("@Avatar", SqlDbType.Int);
            commandParameters[2].Value = data.Avatar;
            commandParameters[3] = new SqlParameter("@Theme", SqlDbType.Int);
            commandParameters[3].Value = data.Theme;
            commandParameters[4] = new SqlParameter("@MenuType", SqlDbType.Bit);
            commandParameters[4].Value = data.MenuType;
            commandParameters[5] = new SqlParameter("@MenuHide", SqlDbType.Bit);
            commandParameters[5].Value = data.MenuHide;
            commandParameters[6] = new SqlParameter("@SmsOn", SqlDbType.Bit);
            commandParameters[6].Value = data.SmsOn;
            commandParameters[7] = new SqlParameter("@CallSound", SqlDbType.Int);
            commandParameters[7].Value = data.CallSound;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

